﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using System.Windows.Forms;
using Wise.Util;

namespace Wise.Entities
{
    public class 表结构
    {
        /// <summary>
        /// 创建列定义。用于CreateTable
        /// </summary>
        /// <param name="_sourColumn"></param>
        /// <returns></returns>
        public static string _Create_Column(表映射 _table, 表的列 _sourColumn, string newColumnName, bool isMustNullable = false)
        {
            string dataLength = null;
            if (_sourColumn._IsNeedLength())
            {
                bool needEncrypt = _table.NeedEncrypt(newColumnName);
                if (_sourColumn.长度 == -1)
                    dataLength = "(MAX)";
                else if (needEncrypt)
                {
                    if (_sourColumn.长度 >= 2000)
                        dataLength = "(MAX)";
                    else
                        dataLength = $"({_sourColumn.长度 * 2})";
                }
                else
                    dataLength = $"({_sourColumn.长度})";
            }
            else if (_sourColumn._IsNeedScalar())
            {
                dataLength = $"({_sourColumn.长度},{_sourColumn.精度})";
            }
            else
            {
                dataLength = null;
            }
            string nullable = "NULL";
            //新增字段必须是可空的，否则，新系统会报错
            //isMustNullable || _sourColumn.可空 ? "NULL" : "NOT NULL";
            return ($"[{newColumnName}] [{_sourColumn.类型}]{dataLength} {nullable}");
        }

        public static string _Encode(string sql)
        {
            return new Regex("\'").Replace(sql, "''");
        }

        /// <summary>
        /// 表结构
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> CreateTable(string TableName, 表映射 _table, 表结构 _target)
        {
            List<string> builder = new List<string>();
            if (!_table._目标表._Exists)
            {
                #region 新建表
                builder.Add(string.Format(@"
CREATE TABLE [{0}](
{1}
)
"
, TableName
, string.Join(
    $",{Environment.NewLine}"
    , _table._Mapping.Where(x => x.Item2 != null)
        .Select(x => _Create_Column(_table, x.Item2, x.Item3.新数据库字段)))// $"{MyDbConfig.Current.新增字段前缀}{x.Item3.新数据库字段}")))
));
                builder.Add($"SELECT '新建表[{TableName}]' ");
                var toAdd = this.列
                       .Where(x => !_table.字段映射.Any(y => string.Equals(y.旧数据库字段, x.字段, StringComparison.CurrentCultureIgnoreCase)))
                       .Select(x => new 字段映射()
                       {
                           新数据库字段 = $"{MyDbConfig.Current.新增字段前缀}{x.字段}"
                       ,
                           旧数据库字段 = x.字段
                       }).ToList();

                _table.字段映射.AddRange(toAdd);
                #endregion
            }
            else if (_table.表存在动作 == TableExistsActionType.默认)
            {
                #region 添加所有源表的列，并为其添加前缀
                {
                    var toAdd = this.列.Where(x => !_target.列.Any(y =>
                    string.Equals(y.字段, string.Concat(MyDbConfig.Current.新增字段前缀, x.字段), StringComparison.CurrentCultureIgnoreCase)
                 )).ToList();
                    for (int i = 0; i < toAdd.Count; i++)
                    {
                        var item = toAdd[i];
                        string newColumnName = string.Concat(MyDbConfig.Current.新增字段前缀, item.字段);
                        builder.Add(string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE [dbo].[{0}] ADD {2}"
    , TableName
    , newColumnName
    , _Create_Column(_table, item, newColumnName, true)));
                        builder.Add($"SELECT '新增列[{newColumnName}] 到表[{TableName}]' ");
                        if (!_table.字段映射.Any(x => string.Equals(x.新数据库字段, newColumnName, StringComparison.CurrentCultureIgnoreCase)))
                            _table.字段映射.Add(new Entities.字段映射()
                            {
                                新数据库字段 = newColumnName
                                 ,
                                旧数据库字段 = item.字段
                            });
                    }
                }
                {
                    var toAdd = _table._Mapping
                        .Where(x => x.Item2 != null)
                        .Where(x => !_target.列.Any(y =>
                    string.Equals(y.字段, x.Item3.新数据库字段, StringComparison.CurrentCultureIgnoreCase)
                 )).ToList();
                    for (int i = 0; i < toAdd.Count; i++)
                    {
                        var item = toAdd[i];
                        string newColumnName = item.Item3.新数据库字段;
                        builder.Add(string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE [dbo].[{0}] ADD {2}"
    , TableName
    , newColumnName
    , _Create_Column(_table, item.Item2, newColumnName, true)));
                        builder.Add($"SELECT '新增列[{newColumnName}] 到表[{TableName}]' ");
                    }
                }
                #endregion
            }
            else
            {
                #region 添加不存在的列，并为其添加前缀
                var toAdd = this.列.Where(x => !_target.列.Any(y =>
                string.Equals(y.字段, x.字段, StringComparison.CurrentCultureIgnoreCase)
                 || string.Equals(y.字段, string.Concat(MyDbConfig.Current.新增字段前缀, x.字段), StringComparison.CurrentCultureIgnoreCase)

                )).ToList();
                for (int i = 0; i < toAdd.Count; i++)
                {
                    var item = toAdd[i];
                    var mp = _table.字段映射.FirstOrDefault(x => string.Equals(x.旧数据库字段, item.字段, StringComparison.CurrentCultureIgnoreCase));
                    string newColumnName = string.Concat(MyDbConfig.Current.新增字段前缀, item.字段);
                    if (mp != null)
                    {
                        if (_target.列.Any(x => string.Equals(mp.新数据库字段, x.字段, StringComparison.CurrentCultureIgnoreCase)))
                            continue;
                        newColumnName = mp.新数据库字段;
                    }
                    builder.Add(string.Format(@"ALTER TABLE [dbo].[{0}] ADD {1}"
, TableName
, _Create_Column(_table, item, newColumnName, true)));
                    builder.Add($"SELECT '新增列[{newColumnName}] 到表[{TableName}]' ");
                    if (mp == null)
                    {
                        mp = new Entities.字段映射()
                        {
                            新数据库字段 = newColumnName
                             ,
                            旧数据库字段 = item.字段
                        };
                        _table.字段映射.Add(mp);
                    }
                }
                #endregion
            }
            _table.字段映射.Where(x => string.IsNullOrEmpty(x.旧数据库字段))
                .Where(x => !_target.列.Any(y => string.Equals(y.字段, x.新数据库字段, StringComparison.CurrentCultureIgnoreCase)))
                .ToList()
                .ForEach(x =>
                {
                    _target.列.Add(new 表的列()
                    {
                        字段 = x.新数据库字段
                         ,
                        类型 = "nvarchar"
                         ,
                        长度 = 500
                    });
                    builder.Add(string.Format(@"
IF NOT EXISTS(SELECT * FROM sys.columns WHERE name='{1}' AND object_id=OBJECT_ID('{0}'))
ALTER TABLE [dbo].[{0}]
ADD [{1}] NVARCHAR(500) NULL"
, TableName
, x.新数据库字段));
                });
            return builder;
        }

        /// <summary>
        /// 覆盖或添加主键
        /// </summary>
        /// <returns></returns>
        public List<string> CreatePrimaryKey(string TableName, 表结构 _target, bool isOverride, 表映射 _table)
        {
            return new List<string>();
            //2017-12-20 不再支持创建主键
            List<string> builder = new List<string>();
            List<IGrouping<string, 索引>> keySource = 索引.Where(x => x.主键).GroupBy(x => x.索引名称).ToList();
            List<IGrouping<string, 索引>> keyTarget = new List<IGrouping<string, 索引>>();
            //if (_target._Exists)
            {
                #region 覆盖操作
                //删除现有表的所有主键
                builder.Add(string.Format(@"
DECLARE @PK NVARCHAR(200)
SELECT @PK=[name] FROM sys.[indexes]  
WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND [is_primary_key]=1
IF(@PK IS NOT NULL)
	EXEC ('ALTER TABLE [dbo].[{0}] DROP CONSTRAINT ['+@PK+']')
"
   , TableName));
                //builder.Add($"SELECT '删除表[{TableName}]的主键'");
                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
ALTER TABLE [dbo].[{0}]
ADD CONSTRAINT [{1}] PRIMARY KEY {2} ({3}) 
"
, TableName
, gp.Key
, gp.Any(x => x.索引类型 == 索引类型.聚集索引) ? "" : "NONCLUSTERED"
, string.Join(",", gp.Select(x =>
{
    string orderBy = x.逆序排列 ? "DESC" : "ASC";
    string newCol = _table._Mapping.Where(y => string.Equals(y.Item3.旧数据库字段, x.索引列, StringComparison.CurrentCultureIgnoreCase))
    .Select(y => y.Item3.新数据库字段)
    .FirstOrDefault();
    if (!string.IsNullOrEmpty(newCol))
    {
        return $"[{newCol}] {orderBy} ";
    }
    return $"[{x.索引列}] {orderBy} ";
}))
));
                builder.Add($"SELECT '添加主键[{gp.Key}] 到[{TableName}]'");
            }
            return builder;
        }

        /// <summary>
        /// 外键
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> CreateForeignKey(string TableName, 表结构 _new, bool isOverride)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 外键>> keySource = 外键.GroupBy(x => x.外键名称).ToList();
            List<IGrouping<string, 外键>> keyTarget = new List<IGrouping<string, 外键>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.外键.GroupBy(x => x.外键名称).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{0}]'))
ALTER TABLE [dbo].[{0}] DROP CONSTRAINT [{1}]
"
, TableName
, gp.Key));
                builder.Add($"SELECT '删除表[{TableName}]的外键[{gp.Key}]'");
                builder.Add(string.Format(@"
ALTER TABLE [dbo].[{0}]
ADD CONSTRAINT [{1}] FOREIGN KEY ({2}) 
REFERENCES [{3}]({4})
"
, TableName
, gp.Key
, string.Join(",", gp.Select(x => $"[{x.列名}]"))
, gp.FirstOrDefault().引用表
, string.Join(",", gp.Select(x => $"[{x.引用列名}]"))
));
                builder.Add($"SELECT '添加外键[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }


        /// <summary>
        /// 索引
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> CreateIndex(string TableName, 表结构 _new, bool isOverride, 表映射 _map)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 索引>> keySource = 索引.Where(x => !x.唯一索引 && !x.主键).GroupBy(x => x.索引名称).ToList();
            List<IGrouping<string, 索引>> keyTarget = new List<IGrouping<string, 索引>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.索引.Where(x => !x.唯一索引 && !x.主键).GroupBy(x => x.索引名称).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF EXISTS(SELECT 1 FROM sys.indexes WHERE name='{0}')
DROP INDEX [{0}] ON [dbo].[{1}]
"
, gp.Key
, TableName));
                builder.Add($"SELECT '删除表[{TableName}]的索引[{gp.Key}]'");
                builder.Add(string.Format(@"
CREATE {2} INDEX [0] ON [dbo].[{1}]
(
	{3}
)
"
, gp.Key
, TableName
, gp.FirstOrDefault().索引类型 == 索引类型.聚集索引 ? "CLUSTERED" : "NONCLUSTERED"
, string.Join(",", gp.Select(x =>
{
    string orderBy = x.逆序排列 ? "DESC" : "ASC";
    //Mapping不全
    //var mp = _map._Mapping.FirstOrDefault(y => y.Item3 != null && string.Equals(y.Item3.旧数据库字段, x.索引列, StringComparison.CurrentCultureIgnoreCase));
    return $"[{x.索引列}] {orderBy}";
}))
));
                builder.Add($"SELECT '添加索引[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }


        /// <summary>
        /// 唯一约束
        /// </summary>
        /// <param name="目标表名"></param>
        /// <returns></returns>
        public List<string> CreateUniqueConstraints(string TableName, 表结构 _new, bool isOverride, 表映射 _map)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 索引>> keySource = 索引.Where(x => x.唯一索引).GroupBy(x => x.索引名称).ToList();
            List<IGrouping<string, 索引>> keyTarget = new List<IGrouping<string, 索引>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.索引.Where(x => x.唯一索引).GroupBy(x => x.索引名称).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF  EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND name = N'{1}')
ALTER TABLE [dbo].[{0}] DROP CONSTRAINT [{1}]
"
, TableName
, gp.Key));
                builder.Add($"SELECT '删除表[{TableName}]的唯一约束[{gp.Key}]'");
                builder.Add(string.Format(@"
ALTER TABLE [dbo].[{0}]  ADD  CONSTRAINT [{1}] UNIQUE {2}
(
{3}
)
"
, TableName
, gp.Key
, gp.FirstOrDefault().索引类型 == 索引类型.聚集索引 ? "CLUSTERED" : "NONCLUSTERED"
, string.Join($",{Environment.NewLine}", gp.Select(x =>
{
    string orderBy = x.逆序排列 ? "DESC" : "ASC";
    //var mp = _map._Mapping.FirstOrDefault(y => y.Item3 != null && string.Equals(y.Item3.旧数据库字段, x.索引列, StringComparison.CurrentCultureIgnoreCase));
    return $"[{x.索引列}] {orderBy}";
}))
));
                builder.Add($"SELECT '添加唯一约束[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }

        /// <summary>
        /// Check约束语句 
        /// 唯一约束，在唯一约束处
        /// 外键约束，在外键处
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> CreateCheckConstraints(string TableName, 表结构 _new, bool isOverride)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 约束>> keySource = 检查约束.GroupBy(x => x.约束名).ToList();
            List<IGrouping<string, 约束>> keyTarget = new List<IGrouping<string, 约束>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.检查约束.GroupBy(x => x.约束名).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{0}]'))
ALTER TABLE [dbo].[{0}] DROP CONSTRAINT [{1}]
"
, TableName
, gp.Key));
                builder.Add($"SELECT '删除表[{TableName}]的检查约束[{gp.Key}]'");
                builder.Add(string.Format(@"
ALTER TABLE [dbo].[{0}]  ADD  CONSTRAINT [{1}] CHECK  {2}
"
, TableName
, gp.Key
, gp.FirstOrDefault().约束条件
));
                builder.Add($"SELECT '添加检查约束[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }

        /// <summary>
        /// 默认值约束
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="_new"></param>
        /// <param name="isOverride"></param>
        /// <returns></returns>
        internal IEnumerable<string> CreateDefaultConstraints(string TableName, 表结构 _new, bool isOverride)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 约束>> keySource = 默认值约束.GroupBy(x => x.约束名).ToList();
            List<IGrouping<string, 约束>> keyTarget = new List<IGrouping<string, 约束>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.默认值约束.GroupBy(x => x.约束名).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF  EXISTS (SELECT * FROM sys.check_constraints WHERE object_id = OBJECT_ID(N'[dbo].[{1}]') AND parent_object_id = OBJECT_ID(N'[dbo].[{0}]'))
ALTER TABLE [dbo].[{0}] DROP CONSTRAINT [{1}]
"
, TableName
, gp.Key));
                builder.Add($"SELECT '删除表[{TableName}]的默认值约束[{gp.Key}]'");
                builder.Add(string.Format(@"
ALTER TABLE [dbo].[{0}]  ADD  CONSTRAINT [{1}] DEFAULT  {2} FOR {3}
"
, TableName
, gp.Key
, gp.FirstOrDefault().约束条件
, string.Join(",", gp.Select(x => $"[{x.列名}]"))
));
                builder.Add($"SELECT '添加默认值约束[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }



        /// <summary>
        /// 触发器
        /// </summary>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public List<string> CreateTrigger(string TableName, 表结构 _new, bool isOverride)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 触发器>> keySource = 触发器.GroupBy(x => x.触发器名称).ToList();
            List<IGrouping<string, 触发器>> keyTarget = new List<IGrouping<string, 触发器>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.触发器.GroupBy(x => x.触发器名称).ToList();
                if (!isOverride)
                    keySource.RemoveAll(x => keyTarget.Any(y => y.Key == x.Key));
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                builder.Add(string.Format(@"
IF  EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[{0}]'))
DROP TRIGGER [dbo].[{0}]
"
, gp.Key));
                builder.Add($"SELECT '删除表[{TableName}]的触发器[{gp.Key}]'");
                builder.Add(gp.FirstOrDefault().触发器语句);
                builder.Add($"SELECT '添加唯一约束[{gp.Key}]到表[{TableName}]'");
            }
            return builder;
        }


        public List<string> CreateTableProperty(string TableName, 表结构 _new, bool isOverride, 表映射 _table)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 表属性>> keySource = 属性.GroupBy(x => x.属性名).ToList();
            List<IGrouping<string, 表属性>> keyTarget = new List<IGrouping<string, 表属性>>();
            if (_new != null)
            {
                #region 添新操作
                keyTarget = _new.属性.GroupBy(x => x.属性名).ToList();
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                if (keyTarget.Any(x => x.Key == gp.Key))
                {
                    if (!_table.表存在动作.HasFlag(TableExistsActionType.覆盖额外属性))
                    {
                        continue;
                    }
                    else
                    {
                        builder.Add(string.Format(@"
EXEC sys.sp_dropextendedproperty 
@name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'{0}'
"
   , TableName));
                        builder.Add($"SELECT '删除表[{TableName}]的描述'");

                    }
                }
                builder.Add(string.Format(@"
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'{1}' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'{0}'
"
, TableName
, gp.FirstOrDefault().属性值
));
                builder.Add($"SELECT '添加描述到表[{TableName}]'");
            }
            return builder;
        }

        public List<string> CreateFieldsProperty(string TableName, 表结构 _target, bool isOverride, 表映射 _table)
        {
            List<string> builder = new List<string>();
            List<IGrouping<string, 表的列属性>> keySource = this.列属性.GroupBy(x => x.字段).ToList();
            List<IGrouping<string, 表的列属性>> keyTarget = new List<IGrouping<string, 表的列属性>>();
            if (_target != null)
            {
                #region 添新操作
                keyTarget = _target.列属性.GroupBy(x => x.字段).ToList();
                #endregion
            }
            else
            {
                #region 覆盖操作

                #endregion
            }
            for (int i = 0; i < keySource.Count; i++)
            {
                var gp = keySource[i];
                var fd = _table._Mapping.FirstOrDefault(x => string.Equals(x.Item3.旧数据库字段, gp.Key, StringComparison.CurrentCultureIgnoreCase));
                if (fd == null || string.IsNullOrEmpty(fd.Item3.新数据库字段))
                {
                    //debugger;
                    continue;
                }
                string fieldName = fd.Item3.新数据库字段;
                if (keyTarget.Any(x => x.Key == gp.Key))
                {
                    builder.Add(string.Format(@"
EXEC sys.sp_dropextendedproperty 
@name=N'MS_Description',
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'{0}',
@level2type=N'COLUMN',
@level2name=N'{1}'
"
, TableName
, fieldName));
                }
                builder.Add($"SELECT '删除表[{TableName}]的字段[{gp.Key}]的描述'");

                builder.Add(string.Format(@"
EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', 
@value=N'{1}' , 
@level0type=N'SCHEMA',
@level0name=N'dbo', 
@level1type=N'TABLE',
@level1name=N'{0}'
@level2type=N'COLUMN',
@level2name=N'{2}'
"
, TableName
, gp.FirstOrDefault().属性值
, fieldName
));
                builder.Add($"SELECT '添加描述到表[{TableName}]的字段[{gp.Key}]'");
            }
            return builder;
        }

        public const int _PAGE_SIZE = 100;

        List<Tuple<string, string, object>> _get_For_Cache(字段引用 _field)
        {
            using (IDbCommand cmd = MyDbConfig.Current.TargetConnection.CreateCommand())
            {
                if (string.IsNullOrEmpty(_field.上级字段))
                {
                    cmd.CommandText = string.Format(@"
SELECT [{0}],[{1}],'' as __NULL FROM [{2}]
"
     , _field.引用表匹配项
     , _field.引用字段
     , _field.引用表);
                }
                else
                {
                    cmd.CommandText = string.Format(@"
SELECT [{0}],[{1}],{3} FROM [{2}]
"
   , _field.引用表匹配项
   , _field.引用字段
   , _field.引用表
   , _field.上级字段);
                }
                try
                {
                    using (IDataReader reader = cmd.ExecuteReader())
                    {
                        DataTable dt = DbHelper.DataReaderToDataTable(reader);
                        return dt.Rows.Cast<DataRow>()
                            .Select(x => new Tuple<string, string, object>(x[0].ToString(), x[2].ToString(), x[1]))
                            .ToList();
                    }
                }
                catch (Exception exp)
                {
                    throw new Exception(cmd.CommandText, exp);
                }
            }
        }

        bool _Is_Uploading = false;
        void _TryUploadFile(object par)
        {
            Tuple<string, string, string, 表映射, 字段映射, DataRow, string> tp = par as Tuple<string, string, string, 表映射, 字段映射, DataRow, string>;
            表映射 _table = tp.Item4;
            字段映射 _field = tp.Item5;
            DataRow _row = tp.Item6;
            string expiredFileName = tp.Item1, expiredDir = tp.Item2
                , Id = tp.Item3, expiredValue = tp.Item7;
            if (string.IsNullOrEmpty(Id))
            {
                return;
            }
            //lock (_table._Uploading_Files)
            //{
            //    _table._Uploading_Files.Add(Id);
            //}
            if (MyDbConfig.Current.是测试)
            {
                while (_Is_Uploading)
                {
                    Thread.Sleep(1);
                }
                _Is_Uploading = true;
            }
            #region 尝试上传文件 2017-12-25 检测到导致程序崩溃的异常
            try
            {

                if (!System.IO.File.Exists(expiredFileName))
                {
                    MyLogger.Current._信息($"{expiredFileName} 不存在", "Files");
                    return;
                }
                string fName = System.IO.Path.GetFileName(expiredFileName);
                string newFName = $"{Guid.NewGuid()}{System.IO.Path.GetExtension(expiredFileName)}";
                string responseText = HttpHelper.HttpUploadFile(MyDbConfig.Current.文件服务器, expiredFileName, new System.Collections.Specialized.NameValueCollection());
                #region 上传文件
                {

                    Dictionary<string, dynamic> responseJson = Json.ToObject<Dictionary<string, dynamic>>(responseText);
                    //{"status":0,"message":"","successcount":1,"failedcount":0,"result":["f9597b57-e993-44b0-97c7-417bed08e4e3"]}
                    if (responseJson != null && responseJson.ContainsKey("result"))
                    {
                        dynamic result = responseJson["result"];
                        //Json.ToObject<Dictionary<string, dynamic>>(responseText)["result"].First.ToString() "20db1534-8174-4e88-9fcd-2b0f31c527b5"  dynamic { string}
                        if (result.HasValues)
                        {
                            string newId = result.First.ToString();
                            string CommandText = string.Format(@"
UPDATE {0}
SET {1}='{2}'
WHERE {3}='{4}'
", _table.目标表名
, _field.新数据库字段
, newId
, _field.文件主键
, Id);

                            _table._SQL.Item5.Add(CommandText);
                        }
                    }
                }
                #endregion
                MyLogger.Current._信息($"{expiredDir}   {expiredFileName} ", "Files");
            }
            catch (Exception exp)
            {
                if (!fileException)
                {
                    fileException = true;
                    try
                    {
                        MyLogger.Current._程序异常(exp, "Files");
                    }
                    catch (Exception exp1)
                    {
                        MessageBox.Show($"由于{exp.Message} {exp.StackTrace} \r\n  引发{exp1.Message}{exp1.StackTrace}");
                    }
                }
                MyLogger.Current._警告(Id, "Files");
            }
            finally
            {
                _Is_Uploading = false;
                _table._Uploading_Files_Done++;
                //_table._Uploading_Files.(Id);
            }
            #endregion
        }

        bool fileException = false;

        Dictionary<string, string> hasCopyedDirectories = new Dictionary<string, string>();

        /// <summary>
        /// 
        /// </summary>
        /// <param name="_col">当值为NULL时，存放到文件暂存夹</param>
        /// <param name="_target"></param>
        /// <param name="_row"></param>
        /// <param name="_table"></param>
        /// <param name="_field"></param>
        /// <returns></returns>
        object _GetValueObject(表的列 _col, 表结构 _target, DataRow _row, 表映射 _table, 字段映射 _field)
        {
            var dd = _row[_field.新数据库字段];
            if (_field.转换 != null)
            {
                MethodInfo caller = typeof(MyConverter).GetMethod(_field.转换.转换名称);
                if (caller == null)
                    throw new Exception($"无法找到[{_field.旧数据库字段}]=>[{_field.新数据库字段}]的转换:[{_field.转换.转换名称}]");
                object val = null;
                try
                {
                    val = caller.Invoke(null, new Object[] { dd, _row });
                }
                catch (Exception exp)
                {
                    throw new Exception($"[{_field.旧数据库字段}]=>[{_field.新数据库字段}]的转换:[{_field.转换.转换名称}]格式错误[{dd}]", exp);
                }
                return NullHelper.IsDbNull(val) ? string.IsNullOrEmpty(_field.默认值) ? "NULL" : _field.默认值 : val;
            }
            else if (NullHelper.IsDbNull(dd))
            {
                #region NULL
                if (!String.IsNullOrEmpty(_field.默认值))
                    return _field.默认值;
                if (_col.可空
                    || _target.默认值约束
                        .Any(x => String.Equals(_col.字段, x.列名, StringComparison.CurrentCultureIgnoreCase)))
                {
                    return "NULL";
                }
                else
                {
                    if (_col._IsString())
                        return "''";
                    else if (_col._IsNumber())
                        return 0;
                    else if (_col._IsDateTime())
                        return DateTime.Now;
                    else if (_col._IsBuffer())
                        //todo:二进制转换
                        return Convert.ToBase64String(new byte[0]);
                    else if (_col._IsTimeStamp())
                        return "DEFAULT";
                    else
                        throw new NotSupportedException(_col.类型);
                }
                #endregion
            }
            else if (!string.IsNullOrEmpty(_field.文件主键))
            {
                #region 转移文档
                string file = dd.ToString();
                if (String.IsNullOrEmpty(file))
                {
                    return null;
                }
                string Id = null;
                if (_row.Table.Columns.Contains(_field.文件主键))
                {
                    Id = _row[_field.文件主键].ToString();
                    if (string.IsNullOrEmpty(Id))
                    {
                        throw new Exception("主键字段应放在第一个映射位置");
                    }
                }
                else
                {
                    Id = Guid.NewGuid().ToString();
                }
                var fd = _table.字段映射.FirstOrDefault(x => x.新数据库字段 == _field.文件主键);
                if (fd == null)
                {
                    //debugger;
                    return "NULL";
                }
                //fd.默认值 = $"'{Id}'";
                //string expiredValue = SecurityHelper.Encrypt(file);
                try
                {
                    _table._Uploading_Files_Total++;
                    //Directory.Exists 只匹配文件夹,File.Exists 只匹配文件
                    if (System.IO.Directory.Exists(file))
                    {
                        if (!hasCopyedDirectories.ContainsKey(file))
                        {
                            hasCopyedDirectories.Add(file, string.Empty);
                            string[] files = System.IO.Directory.GetFiles(file, "*", System.IO.SearchOption.AllDirectories);
                            for (int i = 0; i < files.Length; i++)
                            {
                                //_TryUploadFile(new Tuple<string, string, string, 表映射, 字段映射>(files[i], file, Id.ToString(), _table, _field));
                                ThreadPool.QueueUserWorkItem(_TryUploadFile, new Tuple<string, string, string, 表映射, 字段映射, DataRow, string>(files[i], file, Id.ToString(), _table, _field, _row, Id));
                            }
                        }

                    }
                    else
                    {
                        //_TryUploadFile(new Tuple<string, string, string, 表映射, 字段映射>(file, System.IO.Path.GetDirectoryName(file), Id.ToString(), _table, _field));
                        ThreadPool.QueueUserWorkItem(_TryUploadFile, new Tuple<string, string, string, 表映射, 字段映射, DataRow, string>(file, System.IO.Path.GetDirectoryName(file), Id.ToString(), _table, _field, _row, Id));
                    }
                }
                catch (Exception exp)
                {
                    MyLogger.Current._警告(_table, _col.字段);
                    MyLogger.Current._警告(_table, exp.Message);
                }
                return string.IsNullOrEmpty(_field.默认值) ? "NULL" : _field.默认值;
                //return expiredValue;
                #endregion
            }
            else if (_field.引用 != null)
            {
                #region 引用表
                string _RefValue = dd.ToString();
                if (string.IsNullOrEmpty(_RefValue))
                {
                    if (string.IsNullOrEmpty(_field.默认值))
                        return "NULL";
                    return _field.默认值;
                }
                if (string.Equals("DR LAI", _RefValue))
                {

                }
                字段引用 _ref = _field.引用;
                while (_ref != null)
                {
                    List<Tuple<string, string, object>> _cache = CacheHelper.Get<List<Tuple<string, string, object>>, 字段引用>(
                    $"_CACHE_GetValueObject_{_table.目标表名}_{_field.旧数据库字段}_{_ref.上级字段}_{_ref.上级值}_{_ref.引用表}_{_ref.引用字段}_{_ref.引用表匹配项}"
                    , _get_For_Cache
                    , _ref
                    );
                    object val = null;
                    IEnumerable<Tuple<string, string, object>> iq = _cache;
                    if (!string.IsNullOrEmpty(_ref.上级字段) && !string.IsNullOrEmpty(_ref.上级值))
                    {
                        iq = iq.Where(x => string.Equals(x.Item2, _ref.上级值, StringComparison.CurrentCultureIgnoreCase));
                    }
                    val = iq.Where(x => String.Equals(x.Item1, _RefValue, StringComparison.CurrentCultureIgnoreCase)).Select(x => x.Item3).FirstOrDefault();
                    if (NullHelper.IsDbNull(val))
                    {
                        if (string.IsNullOrEmpty(_ref.默认值))
                            _RefValue = "NULL";
                        else
                            _RefValue = _ref.默认值;
                    }
                    else
                    {
                        _RefValue = val.ToString();
                    }
                    if (string.IsNullOrEmpty(_RefValue))
                    {
                        if (string.IsNullOrEmpty(_ref.默认值))
                            _RefValue = "NULL";
                        else
                            _RefValue = _ref.默认值;
                    }
                    _ref = _ref.引用;
                }
                return _RefValue;
                #endregion
            }
            else if (_field.组合字段 != null)
            {
                #region 组合字段
                MethodInfo caller = typeof(MyConverter).GetMethod(_field.组合字段.转换名称);
                var myParas = caller.GetParameters();
                List<object> paras = new List<object>();
                for (int i = 0; i < _field.组合字段.转换参数.Count; i++)
                {
                    var x = _field.组合字段.转换参数[i];
                    var obj = _row[x].ChangeTo(myParas[i].ParameterType);
                    paras.Add(obj);
                }
                object val = caller.Invoke(null, paras.ToArray());
                return NullHelper.IsDbNull(val) ? "NULL" : val;
                #endregion
            }
            if (_col != null)
            {
                if (_col._IsBuffer())
                {
                    return $"CONVERT(VARBINARY(MAX),'{dd}',2)";
                    //throw new NotImplementedException();
                }
            }
            return dd;
        }

        string _GetValue(表的列 _newCol, 表结构 _newTable, DataRow _row, 表映射 _table, 字段映射 _field)
        {
            try
            {
                object obj = _GetValueObject(_newCol, _newTable, _row, _table, _field);
                if (obj is DateTime || obj is DateTime?)
                {
                    return String.Format("'{0}'", ((DateTime)obj).ToString("yyyy-MM-dd HH:mm:ss.fff"));
                }

                if (obj == null)
                {
                    return null;
                }
                else
                {
                    string ret = obj.ToString();
                    if (ret == "NULL" || ret == "''")
                        return ret;
                    if (ret == "")
                        return "''";
                    if (ret[0] == '\'' && ret[ret.Length - 1] == '\'')
                    {
                        return ret;
                    }
                    else if (ret.StartsWith("Convert(", StringComparison.CurrentCultureIgnoreCase))
                    {
                        return ret;
                    }
                    if (_newCol == null)
                    {
                        //debugger;
                    }
                    else
                    {
                        bool needEncrypt = _table.NeedEncrypt(_newCol.字段);
                        if (needEncrypt)
                            return String.Format("'{0}'", _Encode(SecurityHelper.Encrypt(ret)));
                    }
                    if (_newCol._IsNumber())
                    {
                        return ret;
                    }
                    return String.Format("'{0}'", _Encode(ret));
                }
            }
            catch (OperationAbortedException)
            {
                return "NULL";
            }
            catch (Exception exp)
            {
                MyLogger.Current._格式错误(_table, JsonHelper.ToJson_expand(_row.ItemArray), exp);
                throw exp;
            }
        }


        private string _GetValue(表映射 _table, DataRow myRow, string columnName)
        {
            var fd = _table._Mapping.FirstOrDefault(x => x.Item3.新数据库字段 == columnName);
            if (fd == null)
            {
                if (columnName == "_PK")
                {
                    return $"'{myRow["_PK"]}'";
                }
                else
                {
                    MyLogger.Current._警告(_table, $"无法找到映射{columnName}!");
                    return "NULL";
                }
            }
            return _GetValue(fd.Item2, _table._目标表, myRow, _table, fd.Item3);
        }


        internal Dictionary<string, string> CreateInsert(string TableName, 表结构 _target, 表映射 _table)
        {
            if (MyDbConfig.Current.一次性读取所有数据)
            {
                return _CreateInsert_AllForOne(TableName, _target, _table);
            }
            else
            {
                return _CreateInsert_OneForOne(TableName, _target, _table);
            }
        }

        Dictionary<string, string> _CreateInsert_OneForOne(string TableName, 表结构 _target, 表映射 _table)
        {
            Dictionary<string, string> builder = new Dictionary<string, string>();
            if (_table.同步条件.HasFlag(RowConditionalType.新增不存在)
                || _table.同步条件 == RowConditionalType.默认
                || _table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                || _table.同步条件.HasFlag(RowConditionalType.清空目标表))
            {
                MyDbConfig.Current.SetMessageForOperator(_table, $"正在生成INSERT语句!");
                List<string> toAddList = new List<string>(_table._ExistsSourceData);
                if (!_table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                    && !_table.同步条件.HasFlag(RowConditionalType.清空目标表))
                {
                    toAddList.RemoveAll(x => _table._ExistsTargetData.Contains(x));
                }
                if (toAddList.Count > 0)
                {
                    List<DataRow> _SourceRows = new List<DataRow>();
                    #region 获取源数据
                    int pageSize = Math.Min(_PAGE_SIZE, MyDbConfig.Current.最大迁移行数);
                    List<String> builderForSelector = new List<string>();
                    #region 初始化查询语句
                    {
                        string _Selector1 = String.Join(",", _table._Mapping.Select(x =>
                        {
                            if (string.Equals(x.Item3.新数据库字段, "_PK", StringComparison.CurrentCultureIgnoreCase))
                            {
                                //PK
                                return null;
                            }
                            else if (string.IsNullOrEmpty(x.Item3.旧数据库字段))
                            {
                                if (!string.IsNullOrEmpty(x.Item3.默认值))
                                {
                                    return $"{x.Item3.默认值} AS [{x.Item3.新数据库字段}]";
                                }
                                return $"NULL AS [{x.Item3.新数据库字段}]";
                            }
                            else if (string.IsNullOrEmpty(x.Item3.旧数据库字段))
                            {
                                //_PK
                                return null;
                                //return $"{_map.源表匹配项} AS [{x.Item3.新数据库字段}]";
                            }
                            else if (x.Item1 != null && x.Item1._IsBuffer())
                            {
                                return $"CONVERT(NVARCHAR(MAX),[{x.Item3.旧数据库字段}],2) as [{x.Item3.新数据库字段}]";
                            }
                            if (RegexHelper.IsWordsOnly(x.Item3.旧数据库字段))
                            {
                                return $"[{x.Item3.旧数据库字段}] AS [{x.Item3.新数据库字段}]";
                            }
                            return $"{x.Item3.旧数据库字段.MyFormat(MyDbConfig.Current.目标数据库.数据库名称, "目标数据库")} AS [{x.Item3.新数据库字段}]";
                        })
                         .Where(x => x != null));
                        string _Selector = String.Format(@"SELECT {0},UPPER({2}) AS _PK FROM [{1}] WHERE 1=2 "
                             , _Selector1
                             , _table.源表名
                             , _table.源表匹配项
                         );

                        builderForSelector.Add(_Selector);
                    }
                    #endregion

                    for (int i = 0; i < toAddList.Count && i < MyDbConfig.Current.最大迁移行数; i += pageSize)
                    {
                        MyDbConfig.Current.SetMessageForOperator(_table, $"{i}/{Math.Min(toAddList.Count, MyDbConfig.Current.最大迁移行数)} 正在设置Insert语句!");
                        //无法使用JOIN JOIN F_Split(@IdList,',') f ON f._Item = ({ 1})
                        //Id中存在逗号，无法处理
                        var lst = toAddList.Skip(i).Take(pageSize).ToList();
                        string commandText = String.Format(@"{2} 
OR ({0}) IN ({1})"
                            , _table.源表匹配项
                            , string.Join(",", lst.Select(x => $"'{_Encode(x)}'"))
                            , builderForSelector[0]);
                        //多个Or连接会导致占用内存过高
                        using (IDbCommand cmd = MyDbConfig.Current.SourceConnection.CreateCommand())
                        {
                            cmd.CommandTimeout = 20000;
                            cmd.CommandText = commandText;//string.Join("OR", builderForSelector);
                            try
                            {
                                using (IDataReader reader = cmd.ExecuteReader())
                                {
                                    DataTable dt = DbHelper.DataReaderToDataTable(reader);
                                    if (dt.Rows.Count > toAddList.Count)
                                    {
                                        _SourceRows.Add(dt.Rows.Cast<DataRow>().First());
                                    }
                                    else if (dt.Rows.Count < toAddList.Count)
                                    {
                                        //debugger;
                                        _SourceRows.AddRange(dt.Rows.Cast<DataRow>());
                                    }
                                    else
                                    {
                                        _SourceRows.AddRange(dt.Rows.Cast<DataRow>());
                                    }
                                }
                            }
                            catch (Exception exp)
                            {
                                throw new Exception(cmd.CommandText, exp);
                            }
                        }
                    }

                    #endregion
                    #region 生成SQL
                    var lstNotNullColumns = _target.列
                   .Where(x => !x.可空)
                   .Where(x => !_target.默认值约束.Any(y => string.Equals(x.字段, y.列名, StringComparison.CurrentCultureIgnoreCase)))
                   .Where(x => !_table._Mapping.Any(y => y.Item3 != null && string.Equals(x.字段, y.Item3.新数据库字段, StringComparison.CurrentCultureIgnoreCase)))
                   .ToList();
                    //var pk = _target.索引.Where(x => x.主键)
                    //    .Select(x => x.索引列)
                    //    .Select(x =>
                    //    {
                    //        if (_target.列.Any(y => string.Equals(y.字段, x, StringComparison.CurrentCultureIgnoreCase)))
                    //        {
                    //            return x;
                    //        }
                    //        return string.Concat(MyDbConfig.Current.新增字段前缀, x);
                    //    }).ToList();
                    for (int i = 0; i < _SourceRows.Count; i++)
                    {
                        DataRow myRow = _SourceRows[i];
                        #region 查找PK
                        Dictionary<String, string> values = new Dictionary<string, string>();
                        try
                        {
                            _table._Mapping.ForEach(x =>
                            {
                                string value = _GetValue(x.Item2, _table._目标表, myRow, _table, x.Item3);
                                if (_target._Exists)
                                {
                                    values.Add(x.Item3.新数据库字段, value);
                                }
                                else
                                {
                                    values.Add(x.Item3.旧数据库字段, value);
                                }
                            });
                        }
                        catch (Exception exp)
                        {
                            throw exp;
                        }
                        #endregion
                        string pkValue = myRow["_PK"].ToString();
                        pkValue = _Encode(pkValue);
                        if (builder.ContainsKey(pkValue))
                        {
                            //debugger;
                        }
                        else
                        {
                            string insert = String.Format(@"
INSERT INTO [{0}]({1}{3})VALUES({2}{4})
SELECT '{5}'
"
        , _table.目标表名
        , String.Join(",", _table._Mapping.Select(x => $"[{x.Item3.新数据库字段}]"))
        , string.Join(",", values.Values)
        , lstNotNullColumns.Count == 0 ? "" : string.Concat(",", string.Join(",", lstNotNullColumns.Select(x => $"[{x.字段}]")))
        , lstNotNullColumns.Count == 0 ? "" : string.Concat(",", string.Join(",", lstNotNullColumns.Select(x =>
            x._IsGuid() ? Guid.Empty.ToString()
            : x._IsNumber() ? "0"
            : x._IsDateTime() ? "1900-01-01"
            : x._IsTimeStamp() ? "DEFAULT"
            : "''")))
        , pkValue
        );
                            builder.Add(pkValue, insert);
                        }
                    }
                    #endregion
                }
            }
            return builder;
        }

        Dictionary<string, string> _CreateInsert_AllForOne(string TableName, 表结构 _target, 表映射 _table)
        {
            Dictionary<string, string> builder = new Dictionary<string, string>();
            if (_table.同步条件.HasFlag(RowConditionalType.新增不存在)
                || _table.同步条件 == RowConditionalType.默认
                || _table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                || _table.同步条件.HasFlag(RowConditionalType.清空目标表))
            {
                MyDbConfig.Current.SetMessageForOperator(_table, $"正在生成INSERT语句!");
                List<string> toAddList = new List<string>(_table._ExistsSourceData);
                if (!_table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                    && !_table.同步条件.HasFlag(RowConditionalType.清空目标表))
                {
                    toAddList.RemoveAll(x => _table._ExistsTargetData.Contains(x));
                }
                if (toAddList.Count == 0)
                {
                    return builder;
                }
                List<DataRow> _SourceRows = new List<DataRow>();
                #region 读取数据
                {
                    string _Selector1 = String.Join(",", _table._Mapping.Select(x =>
                    {
                        if (string.Equals(x.Item3.新数据库字段, "_PK", StringComparison.CurrentCultureIgnoreCase))
                        {
                            //PK
                            return null;
                        }
                        else if (string.IsNullOrEmpty(x.Item3.旧数据库字段))
                        {
                            if (!string.IsNullOrEmpty(x.Item3.默认值))
                            {
                                return $"{x.Item3.默认值} AS [{x.Item3.新数据库字段}]";
                            }
                            return $"NULL AS [{x.Item3.新数据库字段}]";
                        }
                        else if (x.Item1 != null && x.Item1._IsBuffer())
                        {
                            return $"CONVERT(NVARCHAR(MAX),[{x.Item3.旧数据库字段}],2) as [{x.Item3.新数据库字段}]";
                        }
                        if (RegexHelper.IsWordsOnly(x.Item3.旧数据库字段))
                        {
                            return $"[{x.Item3.旧数据库字段}] AS [{x.Item3.新数据库字段}]";
                        }
                        return $"{x.Item3.旧数据库字段.MyFormat(MyDbConfig.Current.目标数据库.数据库名称, "目标数据库")} AS [{x.Item3.新数据库字段}]";
                    })
                          .Where(x => x != null));
                    string _Selector = String.Format(@"SELECT {0},UPPER({2}) AS _PK FROM [{1}] s"
                         , _Selector1
                         , _table.源表名
                         , _table.源表匹配项
                     );
                    using (IDbCommand cmd = MyDbConfig.Current.SourceConnection.CreateCommand())
                    {
                        cmd.CommandTimeout = 20000;
                        cmd.CommandText = _Selector;
                        try
                        {
                            using (IDataReader reader = cmd.ExecuteReader())
                            {
                                DataTable dt = DbHelper.DataReaderToDataTable(reader);
                                _SourceRows.AddRange(dt.Rows.Cast<DataRow>());
                            }
                        }
                        catch (Exception exp)
                        {
                            throw new Exception(cmd.CommandText, exp);
                        }
                    }
                }
                #endregion

                #region 生成SQL
                var lstNotNullColumns = _target.列
               .Where(x => !x.可空)
               .Where(x => !_target.默认值约束.Any(y => string.Equals(x.字段, y.列名, StringComparison.CurrentCultureIgnoreCase)))
               .Where(x => !_table._Mapping.Any(y => y.Item3 != null && string.Equals(x.字段, y.Item3.新数据库字段, StringComparison.CurrentCultureIgnoreCase)))
               .ToList();
                for (int i = 0; i < _SourceRows.Count; i++)
                {
                    DataRow myRow = _SourceRows[i];
                    string _PK = myRow["_PK"].ToString();
                    if (!toAddList.Contains(_PK))
                    {
                        continue;
                    }
                    #region 查找PK
                    Dictionary<String, string> values = new Dictionary<string, string>();
                    try
                    {
                        for (int j = 0; j < _table._Mapping.Count; j++)
                        {
                            var _mapItem = _table._Mapping[j];
                            string value = _GetValue(_mapItem.Item2, _table._目标表, myRow, _table, _mapItem.Item3);
                            if (_target._Exists)
                            {
                                values.Add(_mapItem.Item3.新数据库字段, value);
                            }
                            else
                            {
                                values.Add(_mapItem.Item3.旧数据库字段, value);
                            }
                        }
                    }
                    catch (Exception exp)
                    {
                        throw exp;
                    }
                    #endregion
                    string pkValue = myRow["_PK"].ToString();
                    pkValue = _Encode(pkValue);
                    if (builder.ContainsKey(pkValue))
                    {
                        //debugger;
                    }
                    else
                    {
                        string insert = String.Format(@"
INSERT INTO [{0}]({1}{3})VALUES({2}{4})
SELECT '{5}'
"
    , _table.目标表名
    , String.Join(",", _table._Mapping.Select(x => $"[{x.Item3.新数据库字段}]"))
    , string.Join(",", values.Values)
    , lstNotNullColumns.Count == 0 ? "" : string.Concat(",", string.Join(",", lstNotNullColumns.Select(x => $"[{x.字段}]")))
    , lstNotNullColumns.Count == 0 ? "" : string.Concat(",", string.Join(",", lstNotNullColumns.Select(x =>
    x._IsGuid() ? Guid.Empty.ToString()
    : x._IsNumber() ? "0"
    : x._IsDateTime() ? "1900-01-01"
    : x._IsTimeStamp() ? "DEFAULT"
    : "''")))
    , pkValue
    );
                        builder.Add(pkValue, insert);
                    }
                }
                #endregion
            }
            return builder;
        }


        internal Dictionary<string, string> CreateDelete(string TableName, 表结构 _target, 表映射 _table)
        {
            Dictionary<string, string> builder = new Dictionary<string, string>();
            if (_table.同步条件.HasFlag(RowConditionalType.清空目标表))
            {
                builder.Add(string.Empty, $"SELECT '清空表{TableName},共计'+CONVERT(NVARCHAR(200),COUNT(*))+'行数据' FROM [{TableName}];DELETE [{TableName}] ");
            }
            else if (_table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加))
            {
                int pageSize = Math.Min(_PAGE_SIZE, MyDbConfig.Current.最大迁移行数);
                List<string> toDelete = _table._ExistsTargetData
                    .Where(x => _table._ExistsSourceData.Any(y => String.Equals(x, y, StringComparison.CurrentCultureIgnoreCase))).ToList();
                List<String> builderForDelete = new List<string>();
                List<String> builderForDelete1 = new List<string>();
                builderForDelete.Add($"DELETE[{ TableName}] WHERE 1=2 ");
                builderForDelete1.Add($"SELECT '删除表{TableName}数据:");
                for (int i = 0; i < toDelete.Count && i < MyDbConfig.Current.最大迁移行数; i += pageSize)
                {
                    var lst = toDelete.Skip(i).Take(pageSize).ToList();
                    string idList = string.Join(",", lst.Select(x => $"'{_Encode(x)}'"));
                    builderForDelete.Add($" ({_table.目标表匹配项}) IN ({idList}) ");
                    builderForDelete1.Add(idList);
                }
                builder.Add(string.Empty,
                    string.Concat(string.Join("OR", builderForDelete), Environment.NewLine, string.Join(",", builderForDelete1))
                    );
            }
            return builder;
        }


        Dictionary<string, string> _CreateUpdate_OneForOne(string TableName, 表结构 _target, 表映射 _table)
        {
            Dictionary<string, string> builder = new Dictionary<string, string>();
            if (_table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                     || _table.同步条件.HasFlag(RowConditionalType.清空目标表))
            {
                return builder;
            }
            if (_table.同步条件.HasFlag(RowConditionalType.覆盖已存在)
                || _table.同步条件 == RowConditionalType.默认)
            {
                DataRow myRow = null;
                MyDbConfig.Current.SetMessageForOperator(_table, $"正在生成UPDATE语句!");
                List<string> toUpdate = _table._ExistsTargetData.Where(x => !_table._ExistsSourceData.Contains(x)).ToList();
                if (toUpdate.Count > 0)
                {
                    string _Selector = String.Format(@"SELECT {0} FROM [{1}] WHERE "
                           , String.Join(",", _table._Mapping.Select(x =>
                           {
                               if (x.Item1 == null || string.IsNullOrEmpty(x.Item1.字段) || x.Item3.新数据库字段 == "_PK")
                               {
                                   if (!string.IsNullOrEmpty(x.Item3.默认值))
                                   {
                                       return $"{x.Item3.默认值} AS [{x.Item3.新数据库字段}]";
                                   }
                                   return $"NULL AS [{x.Item3.新数据库字段}]";
                               }
                               return $"[{x.Item3.旧数据库字段}] AS [{x.Item3.新数据库字段}]";
                           }))
                           , _table.源表名
                       );
                    for (int i = 0; i < toUpdate.Count && i < MyDbConfig.Current.最大迁移行数; i++)
                    {
                        MyDbConfig.Current.SetMessageForOperator(_table, $"{i}/{Math.Min(toUpdate.Count, MyDbConfig.Current.最大迁移行数)} 正在设置Update语句!");
                        string upK = toUpdate[i];
                        upK = _Encode(upK);
                        string gettor = String.Format("{0} {1} = '{2}'"
                           , _Selector
                           , _table.源表匹配项
                           , upK
                           );
                        using (IDbCommand cmd = MyDbConfig.Current.SourceConnection.CreateCommand())
                        {
                            cmd.CommandText = gettor;
                            using (IDataReader reader = cmd.ExecuteReader())
                            {
                                DataTable dt = DbHelper.DataReaderToDataTable(reader);
                                if (dt.Rows.Count == 1)
                                {
                                    myRow = dt.Rows[0];
                                }
                                else if (dt.Rows.Count == 0)
                                {
                                    throw new Exception($"无法在表{_table.源表名} 中找到[{_table.源表匹配项}]='{upK}'的项");
                                }
                                else
                                {
                                    throw new Exception($"在表{_table.源表名} 中找到[{_table.源表匹配项}]='{upK}'的多个项");
                                }
                            }
                        }
                        builder.Add(upK, string.Format(@"
UPDATE [{0}]
SET 
{1}
WHERE {2}='{3}'
SELECT '更新表[{0}]  [{2}]={3}'
"
    , TableName
    , string.Join($",{Environment.NewLine}"
        , _table._Mapping.Where(x => !x.Item3.禁止更新).Select(x =>
          {
              String up = String.Format(@"[{0}]={1}", x.Item3.新数据库字段, _GetValue(x.Item2, _table._目标表, myRow, _table, x.Item3));
              return up;
          }))
    , _table.目标表匹配项
    , upK));
                    }
                }

            }
            return builder;
        }

        Dictionary<string, string> _CreateUpdate_All_For_One(string TableName, 表结构 _target, 表映射 _table)
        {
            Dictionary<string, string> builder = new Dictionary<string, string>();
            if (_table.同步条件.HasFlag(RowConditionalType.删除已存在后再添加)
                     || _table.同步条件.HasFlag(RowConditionalType.清空目标表))
            {
                return builder;
            }
            //默认不再自动更新
            if (_table.同步条件.HasFlag(RowConditionalType.覆盖已存在)
                //|| _table.同步条件 == RowConditionalType.默认
                )
            {
                MyDbConfig.Current.SetMessageForOperator(_table, $"正在生成UPDATE语句!");
                DataTable _SourceRows = new DataTable();
                List<string> toUpdate = _table._ExistsTargetData.Where(x => _table._ExistsSourceData.Contains(x)).ToList();
                if (toUpdate.Count > 0)
                {
                    string _Selector = String.Format(@"SELECT {0},{2} FROM [{1}] "
                           , String.Join(",", _table._Mapping.Select(x =>
                           {
                               if (x.Item1 == null || string.IsNullOrEmpty(x.Item1.字段) || x.Item3.新数据库字段 == "_PK")
                               {
                                   if (!string.IsNullOrEmpty(x.Item3.默认值))
                                   {
                                       return $"{x.Item3.默认值} AS [{x.Item3.新数据库字段}]";
                                   }
                                   return null;
                                   //return $"NULL AS [{x.Item3.新数据库字段}]";
                               }
                               return $"[{x.Item3.旧数据库字段}] AS [{x.Item3.新数据库字段}]";
                           }).Where(x => x != null)
                           )
                           , _table.源表名
                           , $"UPPER({_table.源表匹配项}) AS _PK"
                       );
                    using (IDbCommand cmd = MyDbConfig.Current.SourceConnection.CreateCommand())
                    {
                        cmd.CommandText = _Selector;
                        using (IDataReader reader = cmd.ExecuteReader())
                        {
                            _SourceRows = DbHelper.DataReaderToDataTable(reader);
                        }
                    }
                    //todo:_MyRows存在大量重复数据
                    List<Dictionary<string, string>> _MyRows = _SourceRows.ToList((DataRow myRow, string columnName) =>
                    {
                        return _GetValue(_table, myRow, columnName);
                    });
                    for (int i = 0; i < toUpdate.Count; i++)
                    {
                        string upK = toUpdate[i];
                        string notEncode = $"'{(upK) }'";
                        upK = $"'{_Encode(upK) }'";
                        Dictionary<string, string> myRow = _MyRows.Where(x => x["_PK"] == notEncode || x["_PK"] == upK).FirstOrDefault();
                        if (myRow == null)
                        {
                            List<string> idList = _MyRows.Select(x => x["_PK"]).OrderBy(x => x).ToList();
                            throw new Exception($"无法在数据源找到 [{upK}]!");
                        }
                        builder.Add(upK, string.Format(@"
UPDATE [{0}]
SET 
{1}
WHERE {2}={3}
SELECT '更新表[{0}]  [{2}]={4}'
"
    , TableName
    , string.Join($",{Environment.NewLine}"
        , _table._Mapping.Where(x => !x.Item3.禁止更新).Select(x =>
          {
              if (myRow.ContainsKey(x.Item3.新数据库字段))
              {
                  String up = String.Format(@"[{0}]={1}", x.Item3.新数据库字段, myRow[x.Item3.新数据库字段]);
                  return up;
              }
              else
              {
                  MyLogger.Current._警告($"{x.Item3.新数据库字段}不存在 {_table.源表名}=>{_table.目标表名}", _table.Id.ToString());
                  return null;
              }
          }).Where(x => x != null)
        )
    , _table.目标表匹配项
    , upK
    , upK.Trim('\'')
    ));
                    }
                }

            }
            return builder;
        }

        internal Dictionary<string, string> CreateUpdate(string TableName, 表结构 _target, 表映射 _table)
        {
            if (MyDbConfig.Current.一次性读取所有数据)
            {
                return _CreateUpdate_All_For_One(TableName, _target, _table);
            }
            else
            {
                return _CreateUpdate_OneForOne(TableName, _target, _table);
            }
        }

        [NonSerialized]
        public bool _Exists = false;

        public List<表属性> 属性 { get; private set; } = new List<表属性>();

        public List<索引> 索引 { get; private set; } = new List<索引>();

        public List<外键> 外键 { get; private set; } = new List<外键>();

        public List<约束> 检查约束 { get; private set; } = new List<约束>();

        public List<约束> 默认值约束 { get; private set; } = new List<约束>();

        public List<触发器> 触发器 { get; private set; } = new List<触发器>();

        public List<表的列> 列 { get; private set; } = new List<表的列>();

        public List<表的列属性> 列属性 { get; private set; } = new List<表的列属性>();
    }
}
